package com.rockcent.mall.lib.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import com.rockcent.common.common.PageResult;
import com.rockcent.common.common.SQLListDoing;
import com.rockcent.common.common.SQLObjectDoing;
import com.rockcent.common.common.SQLPageDoing;
import com.rockcent.mall.lib.dto.MerchantCustomerStatisticsDto;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.stereotype.Repository;

import java.util.List;

/**
 * Created by DK on 16/11/16.
 */
@Repository
public class OrderEventRepositoryImpl implements OrderEventRepositoryCustom {

    private static final Logger log = LoggerFactory.getLogger(ProductEventRepositoryImpl.class);

    @Autowired
    private NamedParameterJdbcTemplate jdbcTemplate;
    @Autowired
    private ObjectMapper objectMapper;

    /**
     * 总客户数
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long findBuyerIdNumByMerchantIdAndDate(Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.date_created>=:startDate");
            sql.append(" and oe.date_created<=:endDate");
            sql.append(" and oe.event_type in('ORDERED','PAYED')");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            fields.append("count(distinct mo.customer_id)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 未支付客户
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long findUnpayCustomerNumByMerchantIdAndDate(Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.event_type='ORDERED'");
            sql.append(" and not exists(select e.merchant_order_num from order_event e where oe.merchant_order_num=e.merchant_order_num " +
                    " and e.event_type ='PAYED')");
            sql.append(" and oe.date_created>=:startDate");
            sql.append(" and oe.date_created<=:endDate");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            fields.append("count(distinct mo.customer_id)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 支付新客户
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long findPayNewCustomerNumByMerchantIdAndDate(Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.event_type ='PAYED'");
            sql.append(" and oe.date_created>=:startDate");
            sql.append(" and oe.date_created<=:endDate");
            sql.append(" and mo.customer_id not in(" +
                    "select distinct o.customer_id from order_event e left join merchant_order o on e.merchant_order_num=o.order_num" +
                    " where e.merchant_id=:merchantId " +
                    " and e.mall_id=:mallId" +
                    " and e.event_type ='PAYED'" +
                    " and e.date_created<:startDate" +
                    ")");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            fields.append("count(distinct mo.customer_id)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 支付旧客户
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long findPayOldCustomerNumByMerchantIdAndDate(Long mallId, Long merchantId, String startDate, String endDate) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from order_event oe left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.event_type ='PAYED'");
            sql.append(" and oe.date_created>=:startDate");
            sql.append(" and oe.date_created<=:endDate");
            sql.append(" and mo.customer_id in(" +
                    "select distinct o.customer_id from order_event e left join merchant_order o on e.merchant_order_num=o.order_num" +
                    " where e.merchant_id=:merchantId " +
                    " and e.mall_id=:mallId" +
                    " and e.event_type ='PAYED'" +
                    " and e.date_created<:startDate" +
                    ")");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            fields.append("count(distinct mo.customer_id)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }

    /**
     * 活跃客户
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public PageResult<MerchantCustomerStatisticsDto> findActiveCustomerListByMerchantIdandDate(Long mallId, Long merchantId, String startDate, String endDate, PageResult page) {
        SQLPageDoing<MerchantCustomerStatisticsDto> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from customer c right join " +
                    "(select mo.customer_id," +
                    "sum(case when oe.event_type='ORDERED' then 1 end) as orderNum," +
                    "sum(case when oe.event_type='ORDERED' then moi.buy_count end) as buyNum," +
                    "sum(case when oe.event_type='PAYED' then mo.discount_amount end) as totalAmount," +
                    "sum(case when oe.event_type='ORDERED' then mo.total_amount end) as amount " +
                    " from order_event oe " +
                    " left join merchant_order mo on oe.merchant_order_num=mo.order_num");
            sql.append(" left join merchant_order_item moi on oe.merchant_order_num=moi.merchant_order_num");
            sql.append(" where oe.is_delete='NO'");
            sql.append(" and mo.customer_id is not null");
            sql.append(" and oe.mall_id=:mallId");
            sql.append(" and oe.merchant_id=:merchantId");
            sql.append(" and oe.date_created>=:startDate");
            sql.append(" and oe.date_created<=:endDate");
            sql.append(" and oe.event_type in('ORDERED','PAYED')");
            sql.append(" group by mo.customer_id) m on c.id=m.customer_id order by m.totalAmount desc");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            fields.append(" c.id as customerId,c.nick_name as customerName," +
                    "case when c.mobile is null then '' else c.mobile end as mobile," +
                    "case when m.orderNum is null then 0 else m.orderNum end as orderNum," +
                    "case when m.buyNum is null then 0 else m.buyNum end as buyNum," +
                    "case when m.totalAmount is null then 0 else m.totalAmount end as totalAmount," +
                    "case when m.amount is null then 0 else m.amount end as amount ");
        };
        return result.go(page, MerchantCustomerStatisticsDto.class, objectMapper, jdbcTemplate, log);
    }

    /**
     * 取消订单的购买总数量
     * @param mallId
     * @param merchantId
     * @param startDate
     * @param endDate
     * @return
     */
    @Override
    public Long findCancelOrderNumByMerchantIdandDate(Long mallId, Long merchantId, String startDate, String endDate, Long customerId) {
        SQLObjectDoing<Long> result = (sql, fields, params, typeReferenceMap) -> {
            sql.append("select %s from merchant_order o,merchant_order_item i");
            sql.append(" where o.id = i.merchant_order_id");
            sql.append(" and o.is_delete='NO'");
            sql.append(" and o.mall_id=:mallId");
            sql.append(" and o.merchant_id=:merchantId");
            sql.append(" and o.`status` in('CANCEL')");
            sql.append(" and o.date_created>=:startDate");
            sql.append(" and o.date_created<=:endDate");
            sql.append(" and o.customer_id=:customerId");
            params.put("mallId", mallId);
            params.put("merchantId", merchantId);
            params.put("startDate", startDate);
            params.put("endDate", endDate);
            params.put("customerId",customerId);
            fields.append("sum(i.buy_count)");
        };
        return result.go(Long.class, jdbcTemplate, log, objectMapper);
    }
}
